﻿// -----------------------------------------------------------------------
// <copyright file="RequestFormImpl.cs" company="">
// TODO: Update copyright text.
// </copyright>
// -----------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using StampController.BL.Models;
using StampController.Core;

namespace StampController.BL
{
    /// <summary>
    ///     TODO: Update summary.
    /// </summary>
    public class AuthorizedHistoryImpl
    {
        public bool SaveAuthorized(AuthorizedHistoryModel authorized)
        {
            using (var dbHelper = new DbHelper())
            {
                object[,] parameter;
                int i;
                if (authorized.ID == Guid.Empty)
                {
                    authorized.ID = Guid.NewGuid();
                    parameter = new object[,]
                        {
                            {"@ID", authorized.ID},
                            {"@AuthorizedBy", authorized.AuthorizedBy},
                            {"@AuthorizedPerson", authorized.AuthorizedPerson},
                            {"@StartDate", authorized.StartDate},
                            {"@EndDate", authorized.EndDate},
                            {"@CreatedDate", authorized.CreatedDate},
                            {"@CreatedBy", authorized.CreatedBy},
                            {"@Remark", authorized.Remark}
                        };
                    i = dbHelper.ExecuteNonQuery(@"
                        INSERT INTO AuthorizedHistory (ID, AuthorizedBy, AuthorizedPerson, StartDate, EndDate, CreatedDate, CreatedBy, Remark, Active)
                        VALUES (@ID, @AuthorizedBy, @AuthorizedPerson, @StartDate, @EndDate, @CreatedDate, @CreatedBy, @Remark, 1)", CommandType.Text,
                        parameter);
                }
                else
                {
                    parameter = new object[,]
                        {
                            {"@ID", authorized.ID},
                            {"@ModifiedDate", authorized.ModifiedDate},
                            {"@ModifiedBy", authorized.ModifiedBy},
                            {"@Active", authorized.Active}
                        };
                    i = dbHelper.ExecuteNonQuery(@"
                        UPDATE AuthorizedHistory 
                        SET Active = @Active,
                        ModifiedDate = @ModifiedDate,
                        ModifiedBy = @ModifiedBy
                        WHERE ID = @ID AND Active = 1", CommandType.Text,
                        parameter);
                }
                return i > 0;
            }
        }

        public IList<AuthorizedHistoryModel> GetAuthorizedList(
            string user, int? pageNumber, int? pageSize, out int count)
        {
            try
            {
                using (var dbHelper = new DbHelper())
                {
                    object[,] parameter =
                    {
                        {"@User", user}
                    };

                    using (DataTable dt = dbHelper.GetDataTable(
                        @"SELECT *
                        FROM AuthorizedHistory
                        WHERE Active = 1 AND AuthorizedBy = @User
                        -- AND StartDate >= GETDATE()
                        ORDER BY StartDate DESC", parameter))
                    {
                        var list = dt.Select().Select(row => new AuthorizedHistoryModel
                        {
                            ID = row.Cells<Guid>("ID"),
                            AuthorizedBy = row.Cells<string>("AuthorizedBy"),
                            AuthorizedPerson = row.Cells<string>("AuthorizedPerson"),
                            StartDate = row.Cells<DateTime>("StartDate"),
                            EndDate = row.Cells<DateTime>("EndDate"),
                            CreatedDate = row.Cells<DateTime>("CreatedDate"),
                            CreatedBy = row.Cells<string>("CreatedBy"),
                            ModifiedDate = row.Cells<DateTime?>("ModifiedDate"),
                            ModifiedBy = row.Cells<string>("ModifiedBy")
                        }).ToList();

                        count = list.Count();
                        if (pageNumber.HasValue && pageSize.HasValue)
                            return list.Page(pageNumber.Value, pageSize.Value);
                        return list;
                    }
                }
            }
            catch
            {
                count = 0;
                return new List<AuthorizedHistoryModel>();
            }
        }

        public List<UsersModel> GetAuthorizedPersonList(string user, DateTime now)
        {
            try
            {
                using (var dbHelper = new DbHelper())
                {
                    object[,] parameter =
                    {
                        {"@User", user},
                        {"@Now", now}
                    };

                    using (DataTable dt = dbHelper.GetDataTable(
                        @"SELECT U.UserID, U.DeptID, U.IsHOD, U.IsGD, U.IsAdminSite, U.IsAdminOfficer
                        FROM AuthorizedHistory A
                        LEFT JOIN Users U ON A.AuthorizedBy = U.UserID
                        WHERE 1 IN (U.IsHOD, U.IsGD, U.IsAdminSite, U.IsAdminOfficer) AND A.Active = 1 AND AuthorizedPerson = @User AND @Now BETWEEN StartDate AND EndDate", parameter))
                    {
                        var list = dt.Select().Select(row => new UsersModel
                        {
                            UserID = row.Cells<string>("UserID"),
                            DeptID = row.Cells<int>("DeptID"),
                            IsHOD = row.Cells<bool>("IsHOD"),
                            IsGD = row.Cells<bool>("IsGD"),
                            IsAdminSite = row.Cells<bool>("IsAdminSite"),
                            IsAdminOfficer = row.Cells<bool>("IsAdminOfficer")
                        }).ToList();

                        return list;
                    }
                }
            }
            catch
            {
                return new List<UsersModel>();
            }
        }

        public UsersModel GetAuthorizedPerson(string user, DateTime now)
        {
            UsersModel person = null;
            try
            {
                using (var dbHelper = new DbHelper())
                {
                    object[,] parameter =
                    {
                        {"@User", user},
                        {"@Now", now}
                    };

                    using (var reader = dbHelper.ExecuteReader(
                        @"SELECT MAX(CAST(U.IsHOD | U.IsGD AS INT)) AS ApprovalPermit, MAX(CAST(U.IsAdminSite | U.IsAdminOfficer AS INT)) AS AdminPermit
                        FROM AuthorizedHistory A
                        LEFT JOIN Users U ON A.AuthorizedBy = U.UserID
                        WHERE 1 IN (U.IsHOD, U.IsGD, U.IsAdminSite, U.IsAdminOfficer) AND A.Active = 1 AND AuthorizedPerson = @User AND @Now BETWEEN StartDate AND EndDate", parameter))
                    {
                        if (reader.HasRows && reader.Read())
                        {
                            person = new UsersModel
                            {
                                UserID = user,
                                IsHOD = Convert.ToBoolean(reader.Get<int>(0)),
                                IsAdminSite = Convert.ToBoolean(reader.Get<int>(1))
                            };
                        }

                        reader.Close();
                    }

                    return person;
                }
            }
            catch
            {
                return new UsersModel();
            }
        }

        /// <summary>
        /// Get authorized person by dept
        /// </summary>
        /// <param name="user"></param>
        /// <param name="now"></param>
        /// <param name="deptId"></param>
        /// <returns></returns>
        public UsersModel GetAuthorizedPerson(string user, DateTime now, int? deptId)
        {
            UsersModel person = null;
            try
            {
                using (var dbHelper = new DbHelper())
                {
                    object[,] parameter =
                    {
                        {"@User", user},
                        {"@Now", now},
                        {"@DeptID", deptId}
                    };

                    using (var reader = dbHelper.ExecuteReader(
                        @"SELECT TOP 1 U.UserID, U.DeptID, U.IsHOD, U.IsGD, U.IsAdminSite, U.IsAdminOfficer, U.Email, U.Name
                        FROM AuthorizedHistory A
                        LEFT JOIN Users U ON A.AuthorizedBy = U.UserID AND U.DeptID = @DeptID
                        WHERE 1 IN (U.IsHOD, U.IsGD) 
                        AND A.Active = 1 AND AuthorizedPerson = @User 
                        AND @Now BETWEEN StartDate AND EndDate", parameter))
                    {
                        if (reader.HasRows && reader.Read())
                        {
                            person = new UsersModel
                            {
                                UserID = reader.Get<string>(0),
                                DeptID = reader.Get<int>(1),
                                IsHOD = reader.Get<bool>(2),
                                IsGD = reader.Get<bool>(3),
                                IsAdminSite = reader.Get<bool>(4),
                                IsAdminOfficer = reader.Get<bool>(5),
                                Email = reader.Get<string>(6),
                                Name = reader.Get<string>(7)
                            };
                        }

                        reader.Close();
                    }

                    return person;
                }
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// Get authorized person by dept
        /// </summary>
        /// <param name="user"></param>
        /// <param name="now"></param>
        /// <param name="siteId"></param>
        /// <returns></returns>
        public UsersModel GetDelegateAdmin(string user, DateTime now, int? siteId)
        {
            UsersModel person = null;
            try
            {
                using (var dbHelper = new DbHelper())
                {
                    object[,] parameter =
                    {
                        {"@User", user},
                        {"@Now", now},
                        {"@SiteID", siteId}
                    };

                    using (var reader = dbHelper.ExecuteReader(
                        @"SELECT TOP 1 U.UserID, U.DeptID, U.IsHOD, U.IsGD, U.IsAdminSite, U.IsAdminOfficer, U.Email, U.Name
                        FROM AuthorizedHistory A
                        LEFT JOIN Users U ON A.AuthorizedBy = U.UserID AND U.SiteID = @SiteID
                        WHERE 1 IN (U.IsAdminSite, U.IsAdminOfficer) 
                        AND A.Active = 1 AND AuthorizedPerson = @User 
                        AND @Now BETWEEN StartDate AND EndDate", parameter))
                    {
                        if (reader.HasRows && reader.Read())
                        {
                            person = new UsersModel
                            {
                                UserID = reader.Get<string>(0),
                                DeptID = reader.Get<int>(1),
                                IsHOD = reader.Get<bool>(2),
                                IsGD = reader.Get<bool>(3),
                                IsAdminSite = reader.Get<bool>(4),
                                IsAdminOfficer = reader.Get<bool>(5),
                                Email = reader.Get<string>(6),
                                Name = reader.Get<string>(7)
                            };
                        }

                        reader.Close();
                    }

                    return person;
                }
            }
            catch
            {
                return null;
            }
        }

        public AuthorizedHistoryModel GetAuthorized(Guid id)
        {
            AuthorizedHistoryModel authorized = null;
            using (var dbHelper = new DbHelper())
            {
                object[,] parameter =
                {
                    {"@Id", id}
                };
                using (DbDataReader reader =
                    dbHelper.ExecuteReader(
                        "SELECT A.ID, AuthorizedBy, AuthorizedPerson, StartDate, EndDate, CreatedDate, CreatedBy, ModifiedDate, ModifiedBy, Active, Remark, U.Name AS CreatedName, B.Email AS DelegateMail " +
                        "FROM AuthorizedHistory A " +
                        "LEFT JOIN Users U ON A.CreatedBy = U.UserID " +
                        "LEFT JOIN Users B ON A.AuthorizedPerson = B.UserID " +
                        "WHERE ID = @Id",
                        parameter))
                {
                    if (reader.HasRows && reader.Read())
                    {
                        authorized = new AuthorizedHistoryModel
                        {
                            ID = reader.Get<Guid>(0),
                            AuthorizedBy = reader.Get<string>(1),
                            AuthorizedPerson = reader.Get<string>(2),
                            StartDate = reader.Get<DateTime>(3),
                            EndDate = reader.Get<DateTime>(4),
                            CreatedDate = reader.Get<DateTime>(5),
                            CreatedBy = reader.Get<string>(6),
                            ModifiedDate = reader.Get<DateTime?>(7),
                            ModifiedBy = reader.Get<string>(8),
                            Active = reader.Get<bool>(9),
                            Remark = reader.Get<string>(10),
                            CreatedName = reader.Get<string>(11),
                            DelegateMail = reader.Get<string>(12)
                        };

                        reader.Close();
                    }
                }
            }
            return authorized;
        }

        public List<string> GetDelegateMailList(string user, DateTime now)
        {
            List<string> mailList = null;
            try
            {
                using (var dbHelper = new DbHelper())
                {
                    object[,] parameter =
                    {
                        {"@User", user},
                        {"@Now", now}
                    };

                    using (var reader = dbHelper.ExecuteReader(
                        @"SELECT U.Email
                        FROM AuthorizedHistory A
                        LEFT JOIN Users U ON A.AuthorizedPerson = U.UserID
                        WHERE A.Active = 1 AND AuthorizedBy = @User AND @Now BETWEEN StartDate AND EndDate", parameter))
                    {
                        if (reader.HasRows)
                        {
                            mailList = new List<string>();
                            while (reader.Read())
                            {
                                mailList.Add(reader.Get<string>(0));
                            }
                        }

                        reader.Close();
                    }

                    return mailList;
                }
            }
            catch
            {
                return null;
            }
        }
    }
}